﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Tilver.Data.DataPager
{
    internal class SqlServerPager : IPager
    {
        /// <summary>
        /// 分页语句缓存
        /// </summary>
        private static Dictionary<string, SqlPagerContext> _pagerSqlCache = new Dictionary<string, SqlPagerContext>();
        private static readonly object _lock = new object();
        private readonly SqlPagerContext _context;
        private DbDataBase _dataBase;
        private IList<DbParameter> _params;
        private long _pageSize;

        public SqlServerPager(string sql, long pageSize) : this(sql, pageSize, string.Empty, DbDataFactory.Create()) { }
        public SqlServerPager(string sql, long pageSize, string keyField) : this(sql, pageSize, keyField, DbDataFactory.Create()) { }
        public SqlServerPager(string sql, long pageSize, string keyField, string connectionStringName) : this(sql, pageSize, keyField, DbDataFactory.Create(connectionStringName)) { }
        public SqlServerPager(string sql, long pageSize, DbDataBase database) : this(sql, pageSize, string.Empty, database) { }
        public SqlServerPager(string sql, long pageSize, string keyField, DbDataBase database)
        {
            _dataBase = database;
            _pageSize = pageSize;

            var key = string.Format("{0}_{1}_{2}", database.ConnectionString, sql, pageSize);
            if (!_pagerSqlCache.ContainsKey(key))
            {
                lock (_lock)
                {
                    if (!_pagerSqlCache.ContainsKey(key))
                    {
                        var item = new SqlPagerContext { PageSize = pageSize };
                        var sqlStruct = new SqlParser().Parse(sql);
                        if (string.IsNullOrEmpty(sqlStruct.Select)) sqlStruct.Select = "*";
                        if (!string.IsNullOrEmpty(sqlStruct.Where)) sqlStruct.Where = string.Format("WHERE {0}", sqlStruct.Where);
                        if (!string.IsNullOrEmpty(sqlStruct.GroupBy)) sqlStruct.GroupBy = string.Format("GROUP BY {0}", sqlStruct.GroupBy);
                        if (!string.IsNullOrEmpty(sqlStruct.Having)) sqlStruct.Having = string.Format("HAVING {0}", sqlStruct.Having);
                        if (!string.IsNullOrEmpty(sqlStruct.OrderBy)) sqlStruct.OrderBy = string.Format("ORDER BY {0}", sqlStruct.OrderBy);
                        var fields = SqlPagerUtil.GetFieldsAlias(sqlStruct.Select);
                        var hasGroupBy = !string.IsNullOrEmpty(sqlStruct.GroupBy);

                        // 获得KeyField
                        keyField = (keyField == null) ? string.Empty : keyField.Trim();
                        if (string.IsNullOrEmpty(keyField) && !hasGroupBy)
                        {
                            keyField = SqlPagerUtil.GetKeyField(database, sqlStruct.From);
                        }
                        sqlStruct.From = string.Format("FROM {0}", sqlStruct.From);

                        // RecordCountSql
                        if (hasGroupBy)
                            item.RecordCountSql = string.Format("SELECT COUNT(*) FROM (SELECT {0} {1} {2} {3} {4}) Tilver_Temp_Table_A"
                                , sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having);
                        else
                            item.RecordCountSql = string.Format("SELECT COUNT(*) {0} {1}", sqlStruct.From, sqlStruct.Where);

                        // FirstPageSql
                        if (hasGroupBy)
                            item.FirstPageSql = string.Format("SELECT TOP ({0}) {1} FROM (SELECT {2} {3} {4} {5} {6}) AS Tilver_Temp_Table_A {7}",
                                "@PageSize", fields, sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having, sqlStruct.OrderBy);
                        else
                            item.FirstPageSql = string.Format("SELECT TOP ({0}) {1} {2} {3} {4}", "@PageSize", sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.OrderBy);

                        // PagerSql 
                        var id1 = "((@PageIndex-1)*@PageSize)";
                        var id2 = "(@PageIndex*@PageSize)";
                        //有无关健字段
                        if (string.IsNullOrEmpty(keyField))
                        {
                            //无关键字，有排序（使用ROW_NUMBER方式分页）
                            //if (hasGroupBy)
                            //    sqlStruct.From = string.Format(" FROM (SELECT {0} {1} {2} {3} {4}) AS Temp_Table_A ", sqlStruct.Fields, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having);
                            //else
                            //    sqlStruct.From = string.Format("{0} {1}", sqlStruct.From, sqlStruct.Where);
                            string tmpStr = string.Empty;
                            if (!string.IsNullOrEmpty(sqlStruct.OrderBy))
                            {
                                if (hasGroupBy)
                                {
                                    tmpStr = string.Format("*, ROW_NUMBER() OVER ({0}) AS RowNumber FROM (SELECT {1} {2} {3} {4} {5}) AS Tilver_Temp_Table_A "
                                        , sqlStruct.OrderBy, sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having);
                                }
                                else
                                {
                                    tmpStr = string.Format("{0}, ROW_NUMBER() OVER ({1}) AS RowNumber {2} {3}"
                                        , sqlStruct.Select, sqlStruct.OrderBy, sqlStruct.From, sqlStruct.Where);
                                }
                                item.PagerSql = string.Format("SELECT * FROM (SELECT {0}) AS Tilver_Temp_Table_B WHERE Tilver_Temp_Table_B.RowNumber > {1} AND Tilver_Temp_Table_B.RowNumber <= {2}", tmpStr, id1, id2);
                                //item.PagerSql = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER ({1}) AS RowNumber, {0} {2}) AS Temp_Table_B WHERE Temp_Table_B.RowNumber > {3} AND Temp_Table_B.RowNumber <= {4}"
                                //    , fields, sqlStruct.OrderBy, sqlStruct.From, id1, id2);
                            }
                            else//无关键字，无排序（使用临时表方式分页）
                            {
                                if (hasGroupBy)
                                {
                                    tmpStr = string.Format("* FROM (SELECT {0} {1} {2} {3} {4}) AS Tilver_Temp_Table_A ", sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having);
                                }
                                else
                                {
                                    tmpStr = string.Format("{0} {1} {2}", sqlStruct.Select, sqlStruct.From, sqlStruct.Where);
                                }
                                item.PagerSql = string.Format("SELECT *, RowNumber = IDENTITY(int, 1, 1) INTO #Pager_Tilver_Temp_Table FROM (SELECT TOP 100 PERCENT {0}) Tilver_Temp_Table_B SELECT * FROM #Pager_Tilver_Temp_Table WHERE RowNumber > {1}  AND RowNumber <= {2}"
                                    , tmpStr, id1, id2);

                                //item.PagerSql = string.Format("SELECT ID_IDENTITY = IDENTITY(int, 1, 1), {0} INTO #Pager_Temp_Table FROM (SELECT TOP 100 PERCENT {0} {1}) Temp_Table_B SELECT {0} FROM #Pager_Temp_Table WHERE ID_IDENTITY > {2}  AND ID_IDENTITY <= {3}"
                                //    , fields, sqlStruct.From, id1, id2);
                            }
                        }
                        else
                        {
                            //有关键字（使用TopNotIn方式分页）
                            string tmpWhere = string.IsNullOrEmpty(sqlStruct.Where) ? "WHERE " : sqlStruct.Where + " AND";
                            item.PagerSql = string.Format("SELECT TOP (@PageSize) {0} {1} {2} {3} NOT IN(SELECT TOP {4} {3} {1} {5} {6} {7} {8}) {6} {7} {8}"
                                , sqlStruct.Select, sqlStruct.From, tmpWhere, keyField, id1, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having, sqlStruct.OrderBy);

                            //if (string.IsNullOrEmpty(sqlStruct.OrderBy)) sqlStruct.OrderBy = string.Format("ORDER BY {0}", keyField);
                            //if (hasGroupBy)//有关键字，存在GroupBy子句（使用TopNotIn方式分页）
                            //{
                            //    item.PagerSql = string.Format("SELECT TOP ({0}) {10} FROM (SELECT TOP ({2}) {1} {3} {4} {5} {6} {7}) AS Temp_Table_A WHERE {8} NOT IN(SELECT TOP ({9}) {8} {3} {4} {5} {6} {7}) {7}"
                            //        , pageSize, sqlStruct.Select, id2, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having, sqlStruct.OrderBy, keyField, id1, fields);
                            //}
                            //else
                            //{
                            //    var tmpWhere = string.IsNullOrEmpty(sqlStruct.Where) ? "WHERE " : sqlStruct.Where + " AND";
                            //    //有关键字，不存在GroupBy子句，@sOrder包含关键字（使用MaxMin方式分页）
                            //    if (sqlStruct.OrderBy.IndexOf("ORDER BY " + keyField, StringComparison.CurrentCultureIgnoreCase) == 0)
                            //    {
                            //        var tmp = string.Empty;
                            //        if (sqlStruct.OrderBy.IndexOf("ORDER BY " + keyField + " DESC", StringComparison.CurrentCultureIgnoreCase) == 0)
                            //            tmp = "<(SELECT MIN(";
                            //        else
                            //            tmp = ">(SELECT MAX(";
                            //        item.PagerSql = string.Format("SELECT TOP ({0}) {1} {2} {3} {4} {5} {4}) FROM (SELECT TOP ({6}) {4} {2} {7} {8}) AS Temp_Table_A) {8}"
                            //            , pageSize, sqlStruct.Select, sqlStruct.From, tmpWhere, keyField, tmp, id1, sqlStruct.Where, sqlStruct.OrderBy);
                            //    }
                            //    else//有关键字，不存在GroupBy子句，@sOrder不包含关键字（使用TopNotIn方式分页）
                            //    {
                            //        item.PagerSql = string.Format("SELECT TOP ({0}) {1} {2} {3} {4} NOT IN(SELECT TOP ({5}) {4} {2} {6} {7}) {7}"
                            //            , pageSize, sqlStruct.Select, sqlStruct.From, tmpWhere, keyField, id1, sqlStruct.Where, sqlStruct.OrderBy);
                            //    }
                            //}
                        }
                        _pagerSqlCache.Add(key, item);
                    }
                }
            }
            _context = _pagerSqlCache[key];
        }

        public long GetRecordCount()
        {
            var ado = new DbAdoBase(_context.RecordCountSql, _dataBase);
            var count = ado.AddParameters(_params).ExecuteScalar();
            ado.ClearParameters();
            return Convert.ToInt64(count);
        }

        public DbDataReader GetPageData(long pageIndex)
        {
            DbDataReader ret;
            var temPara = new List<SqlParameter>();
            {
                var p = new SqlParameter("@PageSize", SqlDbType.BigInt);
                p.Value = _pageSize;
                temPara.Add(p);
            }
            var sql = string.Empty;
            if (pageIndex <= 1)
            {
                sql = _context.FirstPageSql;
                var ado = new DbAdoBase(sql, _dataBase);
                ret = ado
                    .AddParameters(_params)
                    .AddParameters(temPara)
                    //.AddInParameter("@PageSize", _pageSize, System.Data.DbType.Int64)
                    .ExecuteReader();
                ado.ClearParameters();
            }
            else
            {
                sql = _context.PagerSql;
                {
                    var p = new SqlParameter("@PageIndex", SqlDbType.BigInt);
                    p.Value = pageIndex; ;
                    temPara.Add(p);
                }
                var ado = new DbAdoBase(sql, _dataBase);
                ret = ado
                    .AddParameters(_params)
                    .AddParameters(temPara)
                    //.AddInParameter("@PageIndex", pageIndex, System.Data.DbType.Int64)
                    //.AddInParameter("@PageSize", _pageSize, System.Data.DbType.Int64)
                    .ExecuteReader();
                ado.ClearParameters();
            }
            return ret;
        }

        ///// <summary>
        ///// 添加分页sql语句参数
        ///// </summary>
        ///// <param name="name"></param>
        ///// <param name="value"></param>
        ///// <param name="dbType"></param>
        ///// <param name="size"></param>
        //public void AddInParameter(string name, object value = null, DbType dbType = DbType.String, int size = 0)
        //{
        //    _params.Add(_context.DataBase.CreateParameter(name, value, dbType, size, ParameterDirection.Input));
        //}

        /// <summary>
        /// 添加分页sql语句参数
        /// </summary>
        /// <param name="paras">参数列表</param>
        public void AddParameters(IList<DbParameter> paras)
        {
            _params = paras;
        }



    }
}
